#coding:utf-8
import pymssql
import pymysql
import requests
import datetime 
import time
import os
import re
import configparser

#常量区
#图片文件取回
URLBEGIn='D:/Download/picture'
#文件域路径
httpBEGIn='http://localhost:8892/uploadfile'
C_CFG=[
    {'name':'高层','moduleid':10850,'catid':15},{'name':'行业','moduleid':10869,'catid':16},
    {'name':'地方','moduleid':10865,'catid':17},{'name':'国际','moduleid':13555,'catid':18},
    {'name':'专家','moduleid':10864,'catid':19},{'name':'创优','moduleid':13556,'catid':20},
    {'name':'人才','moduleid':13557,'catid':152},{'name':'文创','moduleid':13558,'catid':153},
    {'name':'公益','moduleid':13559,'catid':154},{'name':'专题','moduleid':6974,'catid':155},
    {'name':'创投','moduleid':13561,'catid':156},{'name':'装备','moduleid':13562,'catid':157},
    {'name':'线路','moduleid':13563,'catid':158},{'name':'美食','moduleid':13564,'catid':159},
    {'name':'民宿','moduleid':13565,'catid':160},{'name':'特产','moduleid':13566,'catid':161},
    # {'name':'直播','moduleid':7585,'catid':162},{'name':'旅图','moduleid':7006,'catid':163},{'name':'云视频','moduleid':13582,'catid':165}
    {'name':'大数据','moduleid':13560,'catid':164}
    #  高层=10850,行业=10869,地方=10865,国际=13555,专家=10864,
    #  创优=13556,人才=13557,文创=13558,公益=13559,专题=6974,
    #  创投=13561,装备=13562,线路=13563,美食=13564,民宿=13565,
    # 特产=13566,直播=7585,旅图=7006,大数据=13560,云视频=13582
    # /10850,10869,10864,10865,13555,13556,13557,13558,13559,6974,13561,13562,13563,13564,13565,13566,7585,7006,13560,13582
    ]
catid = 163
C_PIC=[
    # {'name':'直播','moduleid':7585,'catid':162},
    # {'name':'云视频','moduleid':13582,'catid':165},
    {'name':'旅图','moduleid':7006,'catid':163},
   
    {'name':'图片速读','moduleid':6603,'catid':168},#catid未知
    {'name':'焦点图','moduleid':6705,'catid':169},#catid未知
    {'name':'图片新闻','moduleid':6706,'catid':170},
    {'name':'摄影大赛','moduleid':7005,'catid':171},
    {'name':'摄影师专区','moduleid':6709,'catid':172},
    {'name':'眼睛想旅行','moduleid':6711,'catid':173},
    {'name':'自然','moduleid':6712,'catid':174},
    {'name':'人文','moduleid':6713,'catid':175},
    {'name':'美食','moduleid':6714,'catid':176},
    {'name':'旅图','moduleid':6715,'catid':177},
    {'name':'局长眼中的世界','moduleid':6717,'catid':178}
    # 6717 
]

def getDbConfig(filename, section):
    config_parser = configparser.ConfigParser()
    config_parser.read(filename)
    database = config_parser.get(section, 'database')
    host = config_parser.get(section, 'host')
    user = config_parser.get(section, 'user')
    password = config_parser.get(section, 'password')
    config = {'database': database, 
        'host': host, 
        'user': user, 
        'password': password}
    return config

def getImageUrl(astr,castr):
    rs=''
    if astr.startswith('http') or  astr.startswith('/Portals/0/C_Info/'):
        rs=astr
    if len(astr)==0 or astr.isspace():
        rs=astr
    else:
        rs='/Portals/0/C_Info/ImageFile/'+castr+'/'+astr
    return rs
def getImageFath(astr,castr):
    rs=''
    if astr.startswith('/Portals/0/C_Info/'):
        rs='/Portals/0/C_Info/ImageFile/'+castr
    if len(astr)==0 or astr.isspace():
        rs=''
    else:
        rs='/Portals/0/C_Info/ImageFile/'+castr
    return rs

def insertData(rs):
    config = getDbConfig('file\database.config', 'DB_MYSQL')
    conn = pymysql.connect(host=config.get('host'),
    user=config.get('user'),
    password=config.get('password'),
    database=config.get('database'),
    charset='utf8')
    cursor = conn.cursor()
    #清空原表数据
    cursor.execute("truncate table v9_news")
    cursor.execute("truncate table v9_news_data")
    cursor.execute("truncate table v9_hits")
    it = 1
    addm=[]
    adddatam=[]
    hitm=[]
    for index,row in enumerate(rs):
        # sql = 'INSERT INTO users (name, lastupdated_at) VALUES(%s, %s)'
        cinfo=getcinfo(row[1])
        # adds = ("INSERT INTO `v9_news`(`id`,`catid`, `typeid`, `title`, `style`, `thumb`, `keywords`, `description`, `posids`, `url`, `listorder`, `status`, `sysadd`, `islink`, `username`, `inputtime`, `updatetime`,  `short_title`, `user_id`) VALUES "
        #     "(%(id)s, %(catid)s, %(typeid)s, %(title)s, %(style)s, %(thumb)s, %(keywords)s, %(description)s, %(posids)s, %(url)s, %(listorder)s, %(status)s, %(sysadd)s, %(islink)s, %(username)s, %(inputtime)s, %(updatetime)s, %(short_title)s,0)")
        # adddata = ("INSERT INTO `v9_news_data`(`id`,`content`, `groupids_view`, `paginationtype`, `maxcharperpage`, `template`,`copyfrom`) VALUES  "
        #     "(%(id)s, %(content)s,'', %(paginationtype)s, %(maxcharperpage)s, %(template)s, %(copyfrom)s)")
        # hitdata = "INSERT INTO `v9_hits`(`hitsid`, `catid`) VALUES (%(hid)s, %(catid)s)"
        adds = ("INSERT INTO `v9_news`(`id`,`catid`, `typeid`, `title`, `style`, `thumb`, `keywords`, `description`, `posids`, `url`, `listorder`, `status`, `sysadd`, `islink`, `username`, `inputtime`, `updatetime`,  `short_title`, `user_id`) VALUES "
            "(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,%s,0)")
        adddata = ("INSERT INTO `v9_news_data`(`id`,`content`,`groupids_view`, `paginationtype`, `maxcharperpage`, `template`,`copyfrom`) VALUES  "
            "(%s, %s, '', %s, %s, %s, %s)")
        hitdata = "INSERT INTO `v9_hits`(`hitsid`, `catid`) VALUES (%s,%s)"
        ind=it+index
        thumbu=getImageUrl(str(row[17]),str(row[1]))
        thumburl=thumbu if(thumbu.startswith('http') or len(thumbu)==0 or thumbu.isspace()) else httpBEGIn+thumbu
        # addvalue={
        #     "id":ind,
        #     "catid":cinfo['catid'],
        #     "typeid":0,
        #     "title":row[5],
        #     "style":'',
        #     # '/Portals/0/C_Info/ImageFile/'+str(cinfo['catid'])+'/'+str(row[17])
        #     # "thumb":'',
        #     "thumb":thumburl,
        #     "keywords":row[10],     
        #     "description":row[7],
        #     "posids":0,
        #     "url": 'http://localhost:8892//index.php?m=content&c=index&a=show&catid='+str(cinfo['catid'])+'&id='+str(ind),
        #     "listorder":0,
        #     "status":99,
        #     "sysadd":1,
        #     "islink":row[12],
        #     "username":'admin',
        #     # time.mktime(datetime.datetime.now().timetuple()) * 1000
        #     "inputtime":time.mktime(row[3].timetuple()),
        #     "updatetime":0,
        #     "short_title":row[6]
        # }
        # adddatavalue={
        #     "id":ind,
        #     "content":getContent(row[8]),
        #     "paginationtype":0,
        #     "maxcharperpage":10000,
        #     "template":'',
        #     "copyfrom":row[9],
        # }
        # hitvalue={
        #     "hid":'c-'+str(cinfo['mid'])+'-'+str(ind),
        #     "catid":cinfo['catid']
        # }
        addmc=(
            ind,
            cinfo['catid'],
            0,
            row[5],
            '',
            # '/Portals/0/C_Info/ImageFile/'+str(cinfo['catid'])+'/'+str(row[17])
            # "thumb":'',
            thumburl,
            row[10],     
            row[7],
            0,
            'http://localhost:8892//index.php?m=content&c=index&a=show&catid='+str(cinfo['catid'])+'&id='+str(ind),
            0,
            99,
            1,
            row[12],
            'admin',
            # time.mktime(datetime.datetime.now().timetuple()) * 1000
            time.mktime(row[3].timetuple()),
            0,
            row[6]
        )
        adddatamc=(
            ind,
            getContent(row[8]),
            0,
            10000,
            '',
            # arrToStrPhoto(array),
            row[9],
        )
        hitmc=(
            'c-'+str(cinfo['mid'])+'-'+str(ind),
            cinfo['catid']
        )
        addm.append(addmc)
        adddatam.append(adddatamc)
        hitm.append(hitmc)
        # cursor.execute(adds, addvalue) # 
        # cursor.execute(adddata, adddatavalue) # 
        # cursor.execute(hitdata, hitvalue) # 
        # it+=len(rs)
    #数据补充
    cursor.executemany(adds,addm)
    cursor.executemany(adddata,adddatam)
    cursor.executemany(hitdata,hitm)
    cursor.execute("update `v9_category` ca set `items`=(select count(t.id) from `v9_news` t where t.catid=ca.catid)")
    conn.commit()
    conn.close()
    print('文章数据迁移完成')

def getcinfo(mouid):
    news={'name':'高层','moduleid':10086,'catid':10}
    for index,cin in enumerate(C_CFG):
        # cin=C_CFG[inedx]
        if mouid==cin['moduleid']:
            news=cin
            break
    mid=1
    if 163==news['catid']:
        mid=3
    if 162==news['catid'] or 165==news['catid']:
        mid=11
    news['mid']=mid
    return news

def getcinfop(mouid):
    news={'name':'城市','moduleid':7006,'catid':163}
    for index,cin in enumerate(C_PIC):
        # cin=C_CFG[inedx]
        if mouid==cin['moduleid']:
            news=cin
            break
    mid=3
    if 163==news['catid']:
        mid=3
    if 162==news['catid'] or 165==news['catid']:
        mid=11
    news['mid']=mid
    return news

def downloadImg(rs):
    for index,row in enumerate(rs):
        if row[17].strip()!='' and row[17].startswith('http')!=True:
            imgurl='http://www.toptour.cn/'+'/Portals/0/C_Info/ImageFile/'+str(row[1])+'/'+str(row[17])
            # imgurl2='http://www.toptour.cn/'
            try:
                pic = requests.get(imgurl, timeout=10)
            except requests.exceptions.ConnectionError:
                print('图片无法下载')
                continue
            
            # path.rsplit("/",1)
            #保存图片路径
            # dir = '/Portals/0/C_Info/ImageFile/'+str(row[1])+'/'+str(row[17])
            print('文件夹qewe',row[17],len(str(row[17])),str(row[17]).isspace())
            dir = URLBEGIn+getImageFath(str(row[17]),str(row[1]))
            print('dir',dir)
            try:
                if not os.path.exists(dir):
                    print('文件夹',dir,'不存在，重新建立')
                    #os.mkdir(file_path)
                    os.makedirs(dir)
                # #获得图片后缀
                # file_suffix = os.path.splitext(img_url)[1]
                # #拼接图片名（包含路径）
                # filename = '{}{}{}{}'.format(file_path,os.sep,file_name,file_suffix)
                # #下载图片，并保存到文件夹中
                # urllib.urlretrieve(img_url,filename=filename)
                fp = open(URLBEGIn+getImageUrl(str(row[17]),str(row[1])), 'wb+')
                fp.write(pic.content)
                fp.close()
            except IOError as e:
                print('文件操作失败',e)
            except Exception as e:
                print('错误 ：',e)

def getContent(content):
    return content.replace('src="/Portals/0/C_Info','src="'+httpBEGIn+'/Portals/0/C_Info')

def insertPicData(rs):
    config = getDbConfig('file\database.config', 'DB_MYSQL')
    conn = pymysql.connect(host=config.get('host'),
    user=config.get('user'),
    password=config.get('password'),
    database=config.get('database'),
    charset='utf8')
    cursor = conn.cursor()
    #清空原表数据
    # cursor.execute("truncate table v9_picture;truncate table v9_picture_data;")
    cursor.execute("delete from v9_picture where id<"+str(len(rs)+2))
    cursor.execute("delete from v9_picture_data where id<"+str(len(rs)+2))
    # cursor.execute("delete from v9_hits where hitsid like 'c-3%'")
    it = 1
    addm=[]
    adddatam=[]
    hitm=[]
    for index,row in enumerate(rs):
        # sql = 'INSERT INTO users (name, lastupdated_at) VALUES(%s, %s)'
        cinfo=getcinfop(row[1])
        # adds = ("INSERT INTO `v9_picture`(`id`,`catid`, `typeid`, `title`, `style`, `thumb`, `keywords`, `description`, `posids`, `url`, `listorder`, `status`, `sysadd`, `islink`, `username`, `inputtime`, `updatetime`,  `short_title`) VALUES "
        #     "(%(id)s, %(catid)s, %(typeid)s, %(title)s, %(style)s, %(thumb)s, %(keywords)s, %(description)s, %(posids)s, %(url)s, %(listorder)s, %(status)s, %(sysadd)s, %(islink)s, %(username)s, %(inputtime)s, %(updatetime)s, %(short_title)s)")
        # adddata = ("INSERT INTO `v9_picture_data`(`id`,`content`,`groupids_view`, `paginationtype`, `maxcharperpage`, `template`,`pictureurls`,`copyfrom`) VALUES  "
        #     "(%(id)s, %(content)s,'', %(paginationtype)s, %(maxcharperpage)s, %(template)s,%(pictureurls)s, %(copyfrom)s)")
        # hitdata = "INSERT INTO `v9_hits`(`hitsid`, `catid`) VALUES (%(hid)s, %(catid)s)"
        adds = ("INSERT INTO `v9_picture`(`id`,`catid`, `typeid`, `title`, `style`, `thumb`, `keywords`, `description`, `posids`, `url`, `listorder`, `status`, `sysadd`, `islink`, `username`, `inputtime`, `updatetime`,  `short_title`) VALUES "
            "(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)")
        adddata = ("INSERT INTO `v9_picture_data`(`id`,`content`,`groupids_view`, `paginationtype`, `maxcharperpage`, `template`,`pictureurls`,`copyfrom`) VALUES  "
            "(%s, %s, '', %s, %s, %s, %s, %s)")
        hitdata = "INSERT INTO `v9_hits`(`hitsid`, `catid`) VALUES (%s,%s)"
        ind=it+index
        thumbu=getImageUrl(str(row[17]),str(row[1]))
        thumburl=thumbu if(thumbu.startswith('http') or len(thumbu)==0 or thumbu.isspace()) else httpBEGIn+thumbu
        array=getUrlsByContent(row[8])
        # addvalue={
        #     "id":ind,
        #     "catid":cinfo['catid'],
        #     "typeid":0,
        #     "title":row[5],
        #     "style":'',
        #     # '/Portals/0/C_Info/ImageFile/'+str(cinfo['catid'])+'/'+str(row[17])
        #     # "thumb":'',
        #     "thumb":thumburl,
        #     "keywords":row[10],     
        #     "description":row[7],
        #     "posids":0,
        #     "url": 'http://localhost:8892//index.php?m=content&c=index&a=show&catid='+str(cinfo['catid'])+'&id='+str(ind),
        #     "listorder":0,
        #     "status":99,
        #     "sysadd":1,
        #     "islink":row[12],
        #     "username":'admin',
        #     # time.mktime(datetime.datetime.now().timetuple()) * 1000
        #     "inputtime":time.mktime(row[3].timetuple()),
        #     "updatetime":0,
        #     "short_title":row[6]
        # }
        addmc=(
            ind,
            cinfo['catid'],
            0,
            row[5],
            '',
            # '/Portals/0/C_Info/ImageFile/'+str(cinfo['catid'])+'/'+str(row[17])
            # "thumb":'',
            thumburl,
            row[10],     
            row[7],
            0,
            'http://localhost:8892//index.php?m=content&c=index&a=show&catid='+str(cinfo['catid'])+'&id='+str(ind),
            0,
            99,
            1,
            row[12],
            'admin',
            # time.mktime(datetime.datetime.now().timetuple()) * 1000
            time.mktime(row[3].timetuple()),
            0,
            row[6]
        )
        # adddatavalue={
        #     "id":ind,
        #     "content":getContent(row[8]),
        #     "paginationtype":0,
        #     "maxcharperpage":10000,
        #     "template":'',
        #     "pictureurls":arrToStrPhoto(array),
        #     "copyfrom":row[9],
        # }
        adddatamc=(
            ind,
            getContent(row[8]),
            0,
            10000,
            '',
            arrToStrPhoto(array),
            row[9],
        )
        # hitvalue={
        #     "hid":'c-'+str(cinfo['mid'])+'-'+str(ind),
        #     "catid":cinfo['catid']
        # }
        hitmc=(
            'c-'+str(cinfo['mid'])+'-'+str(ind),
            cinfo['catid']
        )
        
        addm.append(addmc)
        adddatam.append(adddatamc)
        hitm.append(hitmc)
        # if index>0:
        #     adds+=",%s"
        #     adddata+=",%s"
        #     hitdata+=",%s"
        # cursor.execute(adds, addvalue)  
        # cursor.execute(adddata, adddatavalue)
        # cursor.execute(hitdata, hitvalue) 
        # it+=len(rs)
    #数据补充
    cursor.executemany(adds,addm)
    cursor.executemany(adddata,adddatam)
    # cursor.executemany(hitdata,hitm)
    cursor.execute("update `v9_category` ca set `items`=(select count(t.id) from `v9_picture` t where t.catid=ca.catid);")
    conn.commit()
    conn.close()
    print('图片数据迁移完成')

mysqlConfig = getDbConfig('file\database.config', 'DB_SQLSERVER')
mysqlConn = pymssql.connect(host=mysqlConfig.get('host'),
    user=mysqlConfig.get('user'),
    password=mysqlConfig.get('password'),
    database=mysqlConfig.get('database'),
    charset='utf8')
#获得查询数据
def selectData(sql):
    connetConfig = getDbConfig('file\database.config', 'DB_SQLSERVER')
    connetConn = pymssql.connect(host=connetConfig.get('host'),
        user=connetConfig.get('user'),
        password=connetConfig.get('password'),
        database=connetConfig.get('database'),
        charset='utf8')
    msCursor = connetConn.cursor()
    msCursor.execute(sql)
    #用一个rs变量获取数据
    rs = msCursor.fetchall()
    return rs


def getUrlsByContent(content):
    newcontent=content+"<img"
    # pic_urls = re.findall('/Portals/0/C_Info/ImageFile/.*\.jpg',content, re.S)
    urls = re.findall('src="(.*?)"',content, re.S)
    array=[]
    alts =''
    if 'border="0"></div>' in newcontent:
        alts = re.findall('border="0"></div>(.*?)<img',newcontent, re.S)
    elif '<br />' in newcontent:
        alts = re.findall('<br />(.*?)<img',newcontent, re.S)
    else:
        alts = re.findall('<br>(.*?)<img',newcontent, re.S)
    # print('路径',alts)
    # print('路径2',urls)
    if(len(urls)==len(alts)):
        for index,key in enumerate(urls):
            ar={"url":key,"alt":alts[index]}
            array.append(ar)
    elif(len(urls)>len(alts)):
        for index,key in enumerate(urls):
            if index<len(alts):
                ar={"url":key,"alt":alts[index]}
                array.append(ar)
            else:
                ar={"url":key,"alt":""}
                array.append(ar)
    else:
         for index,key in enumerate(alts):
            if index<len(urls):
                # ar={"url":urls[index],"alt":alts[index]}
                ar={"url":urls[index],"alt":alts[index]}
                array.append(ar)

    # print(array)
    return array

def arrToStrPhoto(array):
    if(len(array)<1):
        return ''
    astr='{'
    for index,row in enumerate(array):
        astr+='\"'+str(index)+'\":{\"url\":\"'+httpBEGIn+row['url']+'\",\"alt\":\"'+zh2u(row['alt'])+'\"}'
        if(index!=(len(array)-1)):
            astr+=','
    astr+='}'
    # print(astr.replace('/','\/'))
    return astr.replace('/','\/')

def getMids(c_f):
    array=[]
    for key in c_f:
        array.append(key['moduleid'])
    return array

def getUnicodeStr(astr):
    return astr.encode('utf-8')
    # return str(astr, "utf-8")
    # return astr.encode('utf-8').decode('utf-8')
    # return astr.decode('utf-8')

def main():    
    #查看连接是否成功
    mysqlCursor = mysqlConn.cursor()
    sql = "select * from c_info t where moduleid in %s ORDER BY infoid ASC"
    wmids=[]
    pmids=[]
    wmids.append(getMids(C_CFG))
    pmids.append(getMids(C_PIC))
    # psql = 'select top 2 * from c_info t where moduleid=6706 ORDER BY infoid desc'
    # mysqlCursor.execute(sql,(getMids(C_CFG),))
    # #用一个rs变量获取数据
    # wrs = mysqlCursor.fetchall()
    # insertData(wrs)
    mysqlCursor.execute(sql,(getMids(C_PIC),))
    #用一个rs变量获取数据
    prs = mysqlCursor.fetchall()
    insertPicData(prs)
    # print(rs[1])
    # downloadImg(rs)

    mysqlCursor.close()
    # insertData(rs)
    print('程序结束')

def zh2u(astr):
    astr=bytes(astr,'unicode_escape').decode('utf-8').replace(r'\u',r'\\u').replace(r'\\\u',r'\\u')
    astr=bytes(astr,'utf-8').decode('unicode_escape')
    return astr.replace('<div align="center">','').replace('<div align="left">','').replace('<br><div>','').replace('<br></div>','').replace('<div>','').replace('</div>','').replace('</div>','').replace('\"','\`') 


if __name__ == '__main__':
    # array=getUrlsByContent('<div align="left">　　金秋十月正是北京最好的季节，秋高气爽，天空高远明净。又恰逢“十一”遇上“中秋”，京城里弥漫着一片浓郁的喜悦气氛。迎接双节的各色花坛仿佛一夜之间出现在了北京城的大街小巷，设计新颖，颜色明艳。花坛映着蓝天，再加上人们洋溢的笑脸，让京城的节日气氛更加鲜明。</div><div align="center"><img title="IMG_3098_副本(0).jpg" src="/Portals/0/C_Info/ImageFile/6706/2017-09/IMG_3098_副本(0).jpg" border="0"></div><div align="center">天安门广场<br></div><div align="center"><img title="IMG_3008_副本.jpg" src="/Portals/0/C_Info/ImageFile/6706/2017-09/IMG_3008_副本.jpg" border="0"></div><div align="center">东单路口<br></div><div align="center"><img title="IMG_3035_副本.jpg" src="/Portals/0/C_Info/ImageFile/6706/2017-09/IMG_3035_副本.jpg" border="0"></div><div align="center">西单路口</div><div align="center"><img title="IMG_3039_副本_编辑.jpg" src="/Portals/0/C_Info/ImageFile/6706/2017-09/IMG_3039_副本_编辑.jpg" border="0"><br></div><div align="center">复兴门</div><div align="center"><img title="IMG_3161_副本.jpg" src="/Portals/0/C_Info/ImageFile/6706/2017-09/IMG_3161_副本.jpg" border="0"><br></div><div align="center">建国门<br></div>')
    # print(array)
    # arrToStrPhoto(array)
    # conteny=getContent('<img title="30984.jpg" src="/Portals/0/C_Info/ImageFile/6706/2014-03/30984.jpg" border="0"><p><br>　　进入春季以来，九华山风景区游客增多，景区景点里的野猴似乎也随旅游旺季开始出动频繁，成为游客眼中的“风景”。<br>　　这些国家二级保护动物——九华灵猴(又称猕猴)，看上去憨态可掬、天真活泼，很招游人喜欢。但景区管理部门还是提醒广大游客，千万不可与野猴戏耍、合影留念，谨防野猴伤人。<br>　　目前，九华山相关部门除加大宣传外，也在重要地段增加人手劝导游客，以进一步确保旅游安全。</p>')
    # print(conteny)
    
    main()
    # mytest()
    # cin=getcinfo(13555)
    # print(cin['catid'])
    # arr=getMids(C_CFG)
    # print(arr)